DROP TABLE SW_CXC2

CREATE TABLE SW_CXC2 AS
SELECT TP_DOC AS TIPO,VENTA_ID AS ORIGEN,DOCUMENTO,NUMEROFISCAL AS FISCAL,SERIE AS DOCTO_S,TIPO AS DOCTO_T,ID AS REFERENCIA,CLIENTE,NOMBRE,VENDEDOR,COBRADOR,SUCURSAL,FECHA,REVISION,VENCIMIENTO
,NOTAPAGO_ID,TP_PAGO,FORMAPAGO,REFERENCIA AS PAGO_REF
,PORC_DES AS DESC1,0 AS DESC2,TOTAL,APLICABLE,YEAR,MES,SYSTIMESTAMP AS ACTUALIZACION
FROM V_MOV_VTA 
WHERE YEAR>=2007 
AND TP_DOC='FAC'
AND ORIGEN='CRE'
UNION
SELECT TP_DOC AS TIPO,PAGO_ID AS ORIGEN,DOCUMENTO,NUMEROFISCAL AS FISCAL,SERIE AS DOCTO_S,TIPO AS DOCTO_T,ID AS REFERENCIA,CLIENTE,NOMBRE,VENDEDOR,COBRADOR,SUCURSAL,FECHA,REVISION,VENCIMIENTO
,NOTAPAGO_ID,TP_PAGO,FORMAPAGO,REFERENCIA AS PAGO_REF
,PORC_DES AS DESC1,0 AS DESC2,-PAGOS AS TOTAL,APLICABLE,YEAR,MES,SYSTIMESTAMP AS ACTUALIZACION
FROM V_MOV_VTA 
WHERE YEAR>=2007 
AND TP_DOC='PAG'
AND ORIGEN='CRE'
UNION
SELECT TP_DOC AS TIPO,NOTA_ID AS ORIGEN,DOCUMENTO,NUMEROFISCAL AS FISCAL,SERIE AS DOCTO_S,TIPO AS DOCTO_T,ID AS REFERENCIA,CLIENTE,NOMBRE,VENDEDOR,COBRADOR,SUCURSAL,FECHA,REVISION,VENCIMIENTO
,NOTAPAGO_ID,TP_PAGO,FORMAPAGO,REFERENCIA AS PAGO_REF
,PORC_DES AS DESC1,0 AS DESC2,TOTAL,APLICABLE,YEAR,MES,SYSTIMESTAMP AS ACTUALIZACION
FROM V_MOV_VTA 
WHERE YEAR>=2007 
AND TP_DOC='NCG'
AND ORIGEN='CRE'
UNION
SELECT TP_DOC AS TIPO,NOTA_ID AS ORIGEN,DOCUMENTO,NUMEROFISCAL AS FISCAL,SERIE AS DOCTO_S,TIPO AS DOCTO_T,ID AS REFERENCIA,CLIENTE,NOMBRE,VENDEDOR,COBRADOR,SUCURSAL,FECHA,REVISION,VENCIMIENTO
,NOTAPAGO_ID,TP_PAGO,FORMAPAGO,REFERENCIA AS PAGO_REF
,PORC_DES AS DESC1,0 AS DESC2,TOTAL,APLICABLE,YEAR,MES,SYSTIMESTAMP AS ACTUALIZACION
FROM V_MOV_VTA 
WHERE YEAR>=2007 
AND TP_DOC='NCR'
AND ORIGEN='CRE'
UNION
SELECT TP_DOC AS TIPO,NOTA_ID AS ORIGEN,DOCUMENTO,NUMEROFISCAL AS FISCAL,SERIE AS DOCTO_S,TIPO AS DOCTO_T,ID AS REFERENCIA,CLIENTE,NOMBRE,VENDEDOR,COBRADOR,SUCURSAL,FECHA,REVISION,VENCIMIENTO
,NOTAPAGO_ID,TP_PAGO,FORMAPAGO,REFERENCIA AS PAGO_REF
,PORC_DES AS DESC1,0 AS DESC2,-DESCTO AS TOTAL,APLICABLE,YEAR,MES,SYSTIMESTAMP AS ACTUALIZACION
FROM V_MOV_VTA 
WHERE YEAR>=2007 
AND TP_DOC='NCD'
AND ORIGEN='CRE'
UNION
SELECT 
'DIS' AS TIPO
,PAGOM_ID AS ORIGEN
,0 AS DOCUMENTO
,0 AS FISCAL
,' ' AS DOCTO_S
,' ' AS DOCTO_T
,PAGOM_ID AS REFERENCIA
,CLAVE AS CLIENTE
,'NOMBRE' AS NOMBRE 
,0 AS VENDEDOR
,0 AS COBRADOR
,1 AS SUCURSAL
,FECHA
,TO_DATE('','DD/MM/YYYY') AS REVISION
,TO_DATE('','DD/MM/YYYY') AS VENCIMIENTO
,0 AS NOTAPAGO_ID
,'U' AS TP_PAGO
,' ' AS FORMAPAGO
,REFERENCIA AS PAGO_REF
,0  AS DESC1,0 AS DESC2
,(A.IMPORTE-NVL((SELECT SUM(X.IMPORTE) FROM SW_PAGOS X WHERE X.PAGOM_ID=A.PAGOM_ID AND X.CLAVE=A.CLAVE AND X.FECHA=A.FECHA),0)) AS TOTAL
,0 AS APLICABLE,YEAR,MES,SYSTIMESTAMP AS ACTUALIZACION
FROM SW_PAGOM A
WHERE YEAR>=2007
AND TIPO IN('N','K')
AND (A.IMPORTE-NVL((SELECT SUM(X.IMPORTE) FROM SW_PAGOS X WHERE X.PAGOM_ID=A.PAGOM_ID AND X.CLAVE=A.CLAVE AND X.FECHA=A.FECHA),0))>1
AND NVL(COMENTARIO,' ') NOT LIKE 'F.fac:%'


CREATE INDEX CXC2_IDX1 ON SW_CXC2(YEAR,MES)

CREATE INDEX CXC2_IDX2 ON SW_CXC2(REFERENCIA,TIPO)


SELECT COUNT(*),SUM(TOTAL) FROM SW_CXC2 WHERE YEAR=2007 AND MES=1 AND TIPO='FAC'

SELECT COUNT(*),SUM(TOTAL) FROM SW_VENTAS WHERE YEAR=2007 AND MES=1 AND ORIGEN='CRE'

SELECT COUNT(*),SUM(TOTAL) FROM SW_CXC2 WHERE YEAR=2007 AND MES=1 AND TIPO='PAG'

SELECT COUNT(*),SUM(-IMPORTE) FROM SW_PAGOS WHERE YEAR=2007 AND MES=1 AND ORIGEN='CRE'

SELECT COUNT(*),SUM(TOTAL) FROM SW_CXC2 WHERE YEAR=2007 AND MES=1 AND TIPO='NCG'

SELECT COUNT(*),SUM(ROUND(IMPORTE*1.15,2)) FROM SW_NOTAS WHERE YEAR=2007 AND MES=1 AND ORIGEN='CRE' AND SERIE='M'

SELECT COUNT(*),SUM(TOTAL) FROM SW_CXC2 WHERE YEAR=2007 AND MES=1 AND TIPO='NCR'

SELECT COUNT(*),SUM(ROUND(IMPORTE*1.15,2)) FROM SW_NOTAS WHERE YEAR=2007 AND MES=1 AND ORIGEN='CRE' AND SERIE NOT IN('M','U','V')

SELECT COUNT(*),SUM(TOTAL) FROM SW_CXC2 WHERE YEAR=2007 AND MES=1 AND TIPO='NCD'

SELECT COUNT(*),SUM(ROUND(IMPORTE*1.15,2)) FROM SW_NOTAS WHERE YEAR=2007 AND MES=1 AND ORIGEN='CRE' AND SERIE  IN('U','V')



SELECT COUNT(*),SUM(TOTAL) FROM SW_CXC2 WHERE YEAR=2007  AND TIPO='FAC'

SELECT COUNT(*),SUM(TOTAL) FROM SW_VENTAS WHERE YEAR=2007  AND ORIGEN='CRE'

SELECT COUNT(*),SUM(TOTAL) FROM SW_CXC2 WHERE YEAR=2007  AND TIPO='PAG'

SELECT COUNT(*),SUM(-IMPORTE) FROM SW_PAGOS WHERE YEAR=2007  AND ORIGEN='CRE'

SELECT COUNT(*),SUM(TOTAL) FROM SW_CXC2 WHERE YEAR=2007 AND TIPO='NCG'

SELECT COUNT(*),SUM(ROUND(IMPORTE*1.15,2)) FROM SW_NOTAS WHERE YEAR=2007  AND ORIGEN='CRE' AND SERIE='M'

SELECT COUNT(*),SUM(TOTAL) FROM SW_CXC2 WHERE YEAR=2007  AND TIPO='NCR'

SELECT COUNT(*),SUM(ROUND(IMPORTE*1.15,2)) FROM SW_NOTAS WHERE YEAR=2007 AND ORIGEN='CRE' AND SERIE NOT IN('M','U','V')

SELECT COUNT(*),SUM(TOTAL) FROM SW_CXC2 WHERE YEAR=2007  AND MES=12 AND TIPO='NCD'

SELECT COUNT(*),SUM(ROUND(IMPORTE*1.15,2)) FROM SW_NOTASDET WHERE YEAR=2007 AND MES=12 AND ORIGEN='CRE' AND SERIE  IN('U','V')

SELECT * FROM SW_NOTASDET WHERE YEAR=2007  AND FECHA='15/12/2007' AND TIPO IN('U','V') AND NOTA_ID NOT IN(
SELECT ORIGEN FROM SW_CXC2 WHERE YEAR=2007  AND TIPO='NCD' AND FECHA='15/12/2007'
)


